package com.navi.loaddata.loadService.impl.edcdata;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.EdcConfig;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

/**
 * @auther Matthew
 * @Date 2018/3/30
 */
@Service
public class SqlUtils {

  Logger logger = LoggerFactory.getLogger(this.getClass());

  protected static final String DCTYPE_PRODUCTION = "Production";
  protected static final String DCTYPE_INSPECTION = "Inspection";

  public String getSqlByConf(List<EdcConfig> edcConfigs, String subEqId, String dctype, String rowNo) throws ETLException {

    if (null == edcConfigs || edcConfigs.size() == 0) {
      throw new ETLException("表edc_config中没有配置!");
    }

    StringBuffer sql = new StringBuffer();
    StringBuffer childSql = new StringBuffer();
    StringBuffer fullColumn = new StringBuffer();
    StringBuffer resultColumn = new StringBuffer();
    StringBuffer mappingColumn = new StringBuffer();
    String resultTableName;
    if (DCTYPE_PRODUCTION.equals(dctype)) {
      resultTableName = "spc_dcProcessDataResult";
    } else if (DCTYPE_INSPECTION.equals(dctype)){
      resultTableName = "spc_dcmeasuredataresult";
    } else {
      throw new ETLException("未知DCTYPE:" + dctype);
    }

    fullColumn.append(" sd.DCDataID, sd.subfactoryname, sd.DCTYPE, sd.MaterialName AS glassId, sd.LotName, ")
        .append(" sd.ProductSpecName AS prodId, sd.ECCODE AS ecCode, sd.ProcessFlowName AS flowId, ")
        .append(" sd.ProcessOperationName AS opeId, sd.MachineName AS lineId, sd.UnitName AS subeqId, ")
        .append(" sd.RecipeName, sd.OwnerCode AS ownerId, sd.MESLINKKEY, sd.EventTime, sd.UpdateTime, sd.EventUser,sd.timekey, ");

    resultColumn.append("DCDATAID, ");

    edcConfigs.forEach(e -> {
      if (rowNo.equals("1")) {
        fullColumn.append("\"").append(e.getResultColumn().replace("_", "")).append("\", ");
        resultColumn.append("\"").append(e.getResultColumn()).append("\" AS \"").append(e.getResultColumn().replace("_", "")).append("\", ");
        mappingColumn.append("('").append(e.getItemName()).append("_").append(e.getSiteName()).append("') AS \"").append(e.getResultColumn()).append("\", ");
      } else {
        String[] fragments = e.getResultColumn().split("_");
        String sResultColumn = "";

        if (fragments != null && fragments.length > 1) {
          sResultColumn = "result_" + String.format("%03d", Integer.parseInt(fragments[1]) - 500);
        } else {
//         TODO
        }
        fullColumn.append("\"").append(sResultColumn.replace("_", "")).append("\", ");
        resultColumn.append("\"").append(sResultColumn).append("\" AS \"").append(sResultColumn.replace("_", "")).append("\", ");
        mappingColumn.append("('").append(e.getItemName()).append("_").append(e.getSiteName()).append("') AS \"").append(sResultColumn).append("\", ");
      }

    });

    childSql.append("SELECT ")
        .append(resultColumn.toString().substring(0, resultColumn.toString().length() - 2))
        .append(" FROM (SELECT DCDATAID, RESULT, ITEMNAME||'_'||SITENAME AS ITEMKEY FROM ")
        .append(resultTableName)
        .append(" WHERE DCDataID = '")
        .append(subEqId)
        .append("') PIVOT (MAX (RESULT) FOR (ITEMKEY) IN (")
        .append(mappingColumn.toString().substring(0, mappingColumn.toString().length() - 2))
        .append("))");


    sql.append("select ").append(fullColumn.toString().substring(0, fullColumn.toString().length() - 2)).append(" from spc_dcdata sd left join ( ")
        .append(childSql)
        .append(" ) sr on sd.DCDATAID = sr.DCDATAID WHERE SD.DCDataID ='").append(subEqId).append("'");

    return sql.toString();
  }

}
